The Custom Column PQL Editor is a script-writing interface with drag and drop functionality; it exposes PQL functions to assist users to configure advanced PQL scripts. It can be opened when configuring Calculated Columns , and from the Master Flow when configuring Messaging nodes and Set Variable nodes.
The PQL Editor exposes the given data set, as well as a range of PQL functions. When creating calculated columns, the PQL Editor exposes the 'Common' PQL functions, the given table and its columns, and variables. Using a point-and-click interface, you can build your script by clicking or dragging and dropping the required functions and metadata onto the script window.
- Click here to learn more about writing scripts in the PQL editor.
- Click here to review the 'Common' PQL functions, including syntax.
Write a Custom Column PQL Script
To open the PQL Editor, click the Advanced Script button (red arrow below) in the Calculated Column window.
The PQL Editor features the script window, along with three panels:
Functions
The Functions panel (red highlight above) exposes Common PQL functions. Add the required function to start writing your expression. To learn more about a function, hover over it to expose its tooltip. The tooltip reveals the function name, the syntax, the value type returned by the function, and description. Right click on any function or function category and click 'Help' to open the relevant documentation in the User Help.
A function can be added by double clicking on it or dragging and dropping it onto the script window. Alternatively, use the intellisense autocompletion functionality; you can begin writing and then press 'Ctrl + space bar' on your keyboard to expose a list of functions that begin with the given text, arranged in alphabetical order. Select the required function from the list.
Once added to the script window, functions are color coded to help to distinguish between different components of the expression. For instance, function names automatically appear in green, text/ string values appear in orange, and numeric values appear in purple
Placeholders
Any required arguments in the selected function are denoted with a placeholder. For example, the AddMonths function is written:
AddMonths(<Member>, <Integer>)
The placeholders in this function are <Member> and <Integer>, which must both be replaced with the required argument to create expression. For example:
AddMonths([data].[dateKey month name].[March], 6)
- Click here to review the 'Common' PQL functions, including syntax.
Columns and Elements
The Columns panel (blue highlight) exposes the columns in the table to which the Calculated Column node was attached, and any variables configured on that column. Open the table to view its columns; when a column is selected its attributes (member elements) will be exposed in the Elements panel (green highlight).
Clicking on Variables in the Columns panel will expose variables that have been configured.
Add a column, element, or variable to the script window by double clicking on it or dragging and dropping it.
- Click here to learn more about the PQL editor.
Save the Custom Script
Once you've written your script in the PQL Editor, click 'Apply'. The script will be added to the Calculated Column window, and the output can be seen in the Preview panel.
